package com.demo.bmsconsole;

/*
 * To modify database, go via adb to (e.g.) /data/data/com.demo.BMS_console_1/databases/MorganDB
 */
import java.util.Random;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class BMSdb {
	// SCHEMA
	public static final String KEY_TITLE = "title";
	public static final String KEY_CONFIG_NAME = "name";
	public static final String KEY_CONFIG_VALUE = "value";
	public static final String KEY_VOLTAGE_VALUE = "value";
	public static final String KEY_CHECKED = "checked";
	public static final String KEY_ROWID = "_id";
	public static final String KEY_CELL_ID = "id";

	// INITIAL CONFIG
	public static final String CFG_INIT_TIMEOUT = "initTimeout";
	public static final int CFG_INIT_TIMEOUT_VAL = 5000;	// ms
		
	// number of batteries
	public static final String CFG_CELL_COUNT = "TotalCells";
	public static final int CFG_CELL_COUNT_NUM = 112;
	// left turn signal
	public static final String CFG_LEFT_TURN = "LeftTurnPin";
	public static final String CFG_LEFT_TURN_PIN = "A1";
	// right turn signal
	public static final String CFG_RIGHT_TURN = "RightTurnPin";
	public static final String CFG_RIGHT_TURN_PIN = "A2";
	// horn
	public static final String CFG_HORN = "HornPin";
	public static final String CFG_HORN_PIN = "A3";
	// direction of car, 0 by default indicates forward, 1 is reverse
	public static final String CFG_DIRECTION = "DirectionPin";
	public static final String CFG_DIRECTION_PIN = "A4";
	// chosen gear (only valid for forward direction), 
	// 0 indicates F2 (default), 1 indicates F1
	public static final String CFG_GEAR = "GearPin";
	public static final String CFG_GEAR_PIN = "A5";
	
	// BMS thresholds
	// Under-voltage - set, ie. when to trigger alert
	public static final String CFG_UV_THRESH_SET = "UVThresholdSet";
	public static final double CFG_UV_THRESH_SET_VAL = 2.5;
	// Under-voltage - clear, ie, when to clear the alert
	public static final String CFG_UV_THRESH_CLEAR = "UVThreshHoldClear";
	public static final double CFG_UV_THRESH_CLEAR_VAL = 2.8;
	// Over-voltage set, ie, when to trigger alert
	public static final String CFG_OV_THRESH_SET = "OVThresholdSet";
	public static final double CFG_OV_THRESH_SET_VAL = 3.8;
	// Over-voltage clear, i.e., when to clear the alert
	public static final String CFG_OV_THRESH_CLEAR = "OVThreshHoldClear";
	public static final double CFG_OV_THRESH_VAL = 3.6;

	// how often to check for cell voltage (in normal, no-alarm conditions).
	// Value in millisecs.
	public static final String CFG_CELL_CHECK_DURATION = "CellCheckDuration";
	public static final int CFG_CELL_CHECK_DURATION_VAL = 1000;   

	public static boolean mFirstTimeInstall = false;

	private static String TAG;
	private static BMSdb singleton;
	private static boolean mDebug = true;
	
	// current list id. set to default list by default.
	public long mCurrentListId = 0;

	private DatabaseHelper mDbHelper;
	private static SQLiteDatabase mDb;

	/**
	 * Database creation definitions
	 */
	private static final String DATABASE_NAME = "MorganDB";
	private static final String TABLE_CONFIG = "config";
	private static final String TABLE_VOLTAGES = "voltages";
	private static final int DATABASE_VERSION = 1;

	private static final String CONFIG_CREATE_SQL = "CREATE TABLE "
			+ TABLE_CONFIG + "( " + KEY_ROWID
			+ " integer primary key autoincrement, " + KEY_CONFIG_NAME
			+ " text not null unique, " + KEY_CONFIG_VALUE + " text not null);";

	private static final String VOLTAGES_CREATE_SQL = "CREATE TABLE "
			+ TABLE_VOLTAGES + " ( " + KEY_ROWID
			+ " integer primary key autoincrement, " + KEY_CELL_ID + " integer not null unique, "
			+ KEY_VOLTAGE_VALUE + " float not null);";

	private static final String CONFIG_QUERY = "SELECT " + KEY_CONFIG_VALUE + 
		" FROM " + TABLE_CONFIG	+ " WHERE " + KEY_CONFIG_NAME + "=";

	private static final String VOLTAGES_QUERY = "SELECT " + KEY_VOLTAGE_VALUE + 
	" FROM " + TABLE_VOLTAGES + " WHERE " + KEY_CELL_ID + "=";

	private final Context mCtx;

	private static class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			Log.i(TAG, "onCreate: " + CONFIG_CREATE_SQL);
			db.execSQL(CONFIG_CREATE_SQL);

			Log.i(TAG, "onCreate: Voltages: " + VOLTAGES_CREATE_SQL);
			db.execSQL(VOLTAGES_CREATE_SQL);

			mFirstTimeInstall = true;
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
					+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONFIG + ";");
			db.execSQL("DROP TABLE IF EXISTS " + TABLE_VOLTAGES + ";");
			onCreate(db);
		}
	}

	public static BMSdb getSingleton(Context ctx) {
		if (singleton == null) {
			singleton = new BMSdb(ctx);
		}
		return singleton;
	}
	/**
	 * Constructor - takes the context to allow the database to be
	 * opened/created
	 * 
	 * @param ctx
	 *            the Context within which to work
	 */
	private BMSdb(Context ctx) {
		this.mCtx = ctx;
		TAG = ctx.getPackageName();
	}

	/**
	 * Open the database. If it cannot be opened, try to create a new
	 * instance of the database. If it cannot be created, throw an exception to
	 * signal the failure
	 * 
	 * @return this (self reference, allowing this to be chained in an
	 *         initialization call)
	 * @throws SQLException
	 *             if the database could be neither opened or created
	 */
	public BMSdb open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		if (mFirstTimeInstall) {
			install();
			Log.i(TAG, "BMSdb.open, first time install done.");
			mFirstTimeInstall = false;
		}
		return this;
	}

	public void close() {
		mDbHelper.close();
		mDb.close();
	}

	/*
	 * insert into  config (name,value) values('count','12');
	 * insert into voltages(id,value) values(i,3.2);
	 */
	
	public void install() {
		if (!mDebug) {
			return;
		}
		
		mDb.beginTransaction();
		try {
			String pre = "insert into " + TABLE_CONFIG + " (" + KEY_CONFIG_NAME + "," + KEY_CONFIG_VALUE +") values ('";
			String post = "');";
			String mid = "', '";
			mDb.execSQL(pre + CFG_INIT_TIMEOUT + mid + CFG_INIT_TIMEOUT_VAL + post);
			mDb.execSQL(pre + CFG_LEFT_TURN + mid + CFG_LEFT_TURN_PIN + post);
			mDb.execSQL(pre + CFG_RIGHT_TURN + mid + CFG_RIGHT_TURN_PIN + post);
			mDb.execSQL(pre + CFG_HORN + mid + CFG_HORN_PIN + post);
			mDb.execSQL(pre + CFG_DIRECTION + mid + CFG_DIRECTION_PIN + post);
			mDb.execSQL(pre + CFG_GEAR + mid + CFG_GEAR_PIN + post);
			mDb.execSQL(pre + CFG_CELL_COUNT + mid + CFG_CELL_COUNT_NUM + post);

			// insert a few values for the batteries
			Random r = new Random(294820);
			for (int i=1;i<=CFG_CELL_COUNT_NUM;i++) {
				double val = (r.nextInt(25))/10.0 + 1.0;
				String s = new String("insert into voltages(id,value) values(" + i + "," + val +");");
				mDb.execSQL(s);
			}
			mDb.setTransactionSuccessful();
		} finally {
			mDb.endTransaction();
		}
	}

	// Return Value of Config variable.
	public static String getConfigValue(String name) {
		String val = null;
		String[] args = {DatabaseUtils.sqlEscapeString(name.toLowerCase())};
		Cursor c = null;
		try {
			c = mDb.rawQuery(CONFIG_QUERY + args[0],null);					
			if (c.getCount() != 1) {
				Log.e(TAG, "getConfigValue - No Config for: " + name);
				return val;
			}
			c.moveToFirst();
			val = c.getString(c.getColumnIndexOrThrow(KEY_CONFIG_VALUE));
		} catch (Exception exc) {
			Log.e(TAG, "getConfigValue, Name=" + name + ":"+ exc.getMessage());
		} finally {
			if (c != null)
				c.close();
		}

		Log.d(TAG, "getConfigValue, val=" + val);
		return val;
	}
	
	// Set Value of a config variable. Overwrite if already there.
	public boolean setConfigValue(String pName, String value) {
		ContentValues cvals = new ContentValues();
		String name = DatabaseUtils.sqlEscapeString(pName.toLowerCase());
		cvals.put(KEY_CONFIG_NAME, name.toLowerCase());
		cvals.put(KEY_CONFIG_VALUE, value);
		mDb.beginTransaction();
		try {
			mDb.delete(TABLE_CONFIG, KEY_CONFIG_NAME +"=*", null);
			// ignore if delete fails.
				
			if (mDb.insert(TABLE_CONFIG,null, cvals) > 0) {		
				mDb.setTransactionSuccessful();
				return true;
			} else {
				return false;
			}
		} catch (Exception e) {
			Log.e(TAG, "setConfigValue, exc: " + e.toString());
			return false;
		}finally {
			mDb.endTransaction();
		}
	}

	public double getVoltage(int id) {
		double voltage = -1;
		String args[] = {Integer.toString(id)};
		Cursor c=null;
		try {
			c = mDb.rawQuery(VOLTAGES_QUERY + args[0],null);
			if (c.getCount() != 1) {
				Log.e(TAG, "getVoltage - No Voltage for: " + id);
				return -1;
			}
			c.moveToFirst();
			voltage = c.getDouble(c.getColumnIndexOrThrow(KEY_VOLTAGE_VALUE));
		} catch (Exception exc) {
			Log.e(TAG, "getVoltage id=" + id + ":" + exc.getMessage());
		} finally {
			if (c!=null) c.close();
		}
	
		Log.d(TAG, "getVoltage: [id:voltage]=" + id + ":" + voltage);
		return voltage;
	}

	public boolean setVoltage(int id, double value) {
		ContentValues cvals = new ContentValues();
		Log.d(TAG,"setVoltage, cell:" + id + ",voltage:" + value);
		cvals.put(KEY_CELL_ID, id);
		cvals.put(KEY_VOLTAGE_VALUE, value);
		mDb.beginTransaction();
		try {
			mDb.delete(TABLE_VOLTAGES, KEY_CELL_ID +"=" + id, null);
			// ignore if delete fails.
				
			if (mDb.insert(TABLE_VOLTAGES, null, cvals) > 0) {
				mDb.setTransactionSuccessful();
				Log.d(TAG,"setVoltage - ok");
				return true;
			} else {
				return false;
			}
		} catch (Exception exc) {
			Log.e(TAG, "setVoltage, exc: " + exc.toString());
			return false;
		}
		finally {
			mDb.endTransaction();
		}
	}
}
